package application;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Greena
 */
public class Database {

	private static Database instance;
	private Connection con = null;

	public Database() {
		try {
			// Statement stmt;
			// Register the JDBC driver for MySQL.
			Class.forName("com.mysql.jdbc.Driver");

			// Define URL of database server for
			// database named mysql on the localhost
			// with the default port number 3306.
			String url = "jdbc:mysql://localhost:3306/javatr";

			// Get a connection to the database for a
			// user named root with a blank password.
			// This user is the default administrator
			// having full privileges to do anything.
			con = DriverManager.getConnection(url, "root", "root");

			// Display URL and connection information
			System.out.println("URL: " + url);
			System.out.println("Connection: " + con);

			// Get a Statement object
			// stmt = con.createStatement();
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("Ceva nu merge..." + e);
		}

	}

	public static Database getInstance() {
		if (instance == null) {
			instance = new Database();
		}
		return instance;
	}

	private void disconectDatabase() {
		instance = null;
		try {
			con.close();
			System.out.println("Disconected");
		} catch (Exception e) {
		}
	}

	public void createUser() {

		try {
			System.out.println("Started");
			String sql = "insert into javatr.user(idUser,username,password,firstName,lastName,"
					+ "homeNumber,cellNumber,email,rank,decription,idUserType)"
					+ "values(3,'x','x','x','x','456','45','x','x','x',1)";
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("Saved");
		} catch (SQLException e) { // TODO: handle exception
			System.out.println(e.toString());
		} finally {
			disconectDatabase();
		}

	}
	/*
	 * public void deleteUser(User user) {
	 * 
	 * //connectToDatabase(); try { String sql =
	 * "delete from TEMA1.LOGIN where USERNAME='" + user.getUser() + "' ";
	 * Statement stmt = con.createStatement(); stmt.executeUpdate(sql);
	 * System.out.println("Deleted"); } catch (SQLException e) { // TODO: handle
	 * exception System.out.println(e.toString()); } finally {
	 * disconectDatabase(); }
	 * 
	 * }
	 * 
	 * public Vector<User> readUser(User user) {
	 * 
	 * Vector<User> util = new Vector<User>(); try { //connectToDatabase();
	 * String sql =
	 * "select USERNAME,ADDRESS,CITY,BIRTH_DAY,TYPE from TEMA1.LOGIN where USERNAME='"
	 * + user.getUser() + "'"; Statement stmt = con.createStatement(); ResultSet
	 * rs = stmt.executeQuery(sql); int nrRow = 0; //Fetch each row from the
	 * result set
	 * 
	 * while (rs.next()) { User c = new User();
	 * 
	 * c.setType(rs.getString("TYPE")); c.setUser(rs.getString("USERNAME"));
	 * 
	 * c.setAddress(rs.getString("ADDRESS")); c.setCity(rs.getString("CITY"));
	 * c.setDate(rs.getString("BIRTH_DAY"));
	 * 
	 * 
	 * nrRow++; util.add(c); } } catch (Exception e) { // TODO: handle exception
	 * // System.out.println("aici"); System.out.println(e.toString());
	 * JOptionPane.showMessageDialog(null,
	 * "Nu ne putem conecta la baza de date !!!"); } finally {
	 * disconectDatabase(); } return util;
	 * 
	 * 
	 * }
	 * 
	 * public Vector<User> readUsers() {
	 * 
	 * Vector<User> util = new Vector<User>(); try { //connectToDatabase();
	 * String sql = "select * from TEMA1.LOGIN"; Statement stmt =
	 * con.createStatement(); ResultSet rs = stmt.executeQuery(sql); int nrRow =
	 * 0; //Fetch each row from the result set
	 * 
	 * while (rs.next()) { User c = new User();
	 * 
	 * c.setType(rs.getString("TYPE")); c.setUser(rs.getString("USERNAME"));
	 * c.setPassword(rs.getString("PASSWORD"));
	 * c.setAddress(rs.getString("ADDRESS")); c.setCity(rs.getString("CITY"));
	 * c.setDate(rs.getString("BIRTH_DAY"));
	 * 
	 * nrRow++; util.add(c); } } catch (Exception e) { // TODO: handle exception
	 * // System.out.println("aici"); System.out.println(e.toString());
	 * JOptionPane.showMessageDialog(null,
	 * "Nu ne putem conecta la baza de date !!!"); } finally {
	 * disconectDatabase(); } return util;
	 * 
	 * 
	 * }
	 * 
	 * public void updateUser(User user) { //connectToDatabase(); try { String
	 * sql = "update TEMA1.LOGIN set PASSWORD='" + user.getPassword() +
	 * "', TYPE='" + user.getType() + "', ADDRESS='" + user.getAddress() +
	 * "', CITY='" + user.getCity() + "', BIRTH_DAY='" + user.getDate() +
	 * "' where USERNAME='" + user.getUser() + "' "; Statement stmt =
	 * con.createStatement(); stmt.executeUpdate(sql);
	 * System.out.println("Saved"); } catch (SQLException e) { // TODO: handle
	 * exception // System.out.println("num"); System.out.println(e.toString());
	 * } finally { disconectDatabase(); } }
	 * 
	 * public String getCityfromUsername(String username) {
	 * 
	 * //connectToDatabase(); try { String sql =
	 * "select CITY from TEMA1.LOGIN where USERNAME='" + username + "'";
	 * Statement stmt = con.createStatement(); ResultSet rs =
	 * stmt.executeQuery(sql); if (rs.next()) { return rs.getString("CITY"); }
	 * else { return "invalid"; } } catch (SQLException e) { // TODO: handle
	 * exception System.out.println(e.toString()); } finally {
	 * disconectDatabase(); } return "invalid";
	 * 
	 * }
	 * 
	 * public String checkLogin(User user) {
	 * 
	 * //connectToDatabase(); try { String sql =
	 * "select TYPE from TEMA1.LOGIN where USERNAME='" + user.getUser() +
	 * "' and PASSWORD='" + user.getPassword() + "'"; Statement stmt =
	 * con.createStatement(); ResultSet rs = stmt.executeQuery(sql); if
	 * (rs.next()) { return rs.getString("TYPE"); } else { return "invalid"; } }
	 * catch (SQLException e) { // TODO: handle exception
	 * System.out.println(e.toString()); } finally { disconectDatabase(); }
	 * return "invalid";
	 */
}